package CRUD;

import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserDao {

    //插入学生信息
    public void addUser(User user)  {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = UserSQL.getConnection();
            String sql = "INSERT INTO Student VALUES (?, ?,?,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, user.getSno());
            pstmt.setString(2, user.getSname());
            pstmt.setString(3, user.getSsex());
            pstmt.setInt(4, user.getSage());
            pstmt.setString(5, user.getSdept());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            UserSQL.close(conn, pstmt, null);
        }
    }

    //更新学生信息
    public void updateUser(User user) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = UserSQL.getConnection();
            String sql = "UPDATE Student SET Sname=?,Ssex=?, Sage=?,Sdept=? WHERE Sno=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, user.getSname());
            pstmt.setString(2, user.getSsex());
            pstmt.setInt(3, user.getSage());
            pstmt.setString(4,user.getSdept());
            pstmt.setInt(5, user.getSno());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            UserSQL.close(conn, pstmt, null);
        }
    }

    //删除学生信息
    public void deleteUser(int Sno) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = UserSQL.getConnection();
            String sql = "DELETE FROM Student WHERE Sno=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, Sno);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            UserSQL.close(conn, pstmt, null);
        }
    }


    //查询学生信息
    public User getUserBySno(int Sno) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        User user=null;
        try {
            conn = UserSQL.getConnection();
            String sql = "SELECT * FROM Student WHERE Sno=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, Sno);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                user = new User();
                user.setSno(rs.getInt("Sno"));
                user.setSname(rs.getString("Sname"));
                user.setSsex(rs.getString("Ssex"));
                user.setSage(rs.getInt("Sage"));
                user.setSdept(rs.getString("Sdept"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            UserSQL.close(conn, pstmt, rs);

        }
        return user;
    }
}
